using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;

/// <summary>
/// Summary description for NewsBll
/// </summary>
public class NewsBll
{
    public NewsBll()
    {
    }

    private const string SQL_SELECT_NEWS = "SELECT * FROM TINTUC WHERE TIEUDE NOT LIKE @TIEUDE ORDER BY ID DESC";
    private const string SQL_SELECT_NEWS_BYID = "SELECT * FROM TINTUC WHERE ID = @ID";
    private const string SQL_SELECT_NEWS_BYTITLE = "SELECT * FROM TINTUC WHERE TIEUDE LIKE @TIEUDE";
    private const string SQL_DELETE_NEWS = "DELETE FROM TINTUC WHERE ID = @ID";
    private const string SQL_INSERT_NEWS = "INSERT INTO TINTUC(TIEUDE, NOIDUNG, NGAYDANG, NGUOIDANG, NGUON, IMAGE1, IMAGE2, IMAGE3, IMAGE4, IMAGE5) VALUES (@TIEUDE, @NOIDUNG, @NGAYDANG, @NGUOIDANG, @NGUON, @IMAGE1, @IMAGE2, @IMAGE3, @IMAGE4, @IMAGE5)";
    private const string SQL_INSERT_TUYENDUNG = "INSERT INTO TINTUC(TIEUDE, NOIDUNG, NGAYDANG) VALUES (@TIEUDE, @NOIDUNG, @NGAYDANG)";
    private const string SQL_UPDATE_NEWS = "UPDATE TINTUC SET TIEUDE = @TIEUDE, NOIDUNG = @NOIDUNG, NGAYDANG = @NGAYDANG, NGUOIDANG = @NGUOIDANG, NGUON = @NGUON, IMAGE1 = @IMAGE1, IMAGE2 = @IMAGE2, IMAGE3 = @IMAGE3, IMAGE4 = @IMAGE4, IMAGE5 = @IMAGE5 WHERE ID = @ID";
    private const string SQL_UPDATE_TUYENDUNG = "UPDATE TINTUC SET TIEUDE = @TIEUDE, NOIDUNG = @NOIDUNG, NGAYDANG = @NGAYDANG WHERE ID = @ID";
    private const string PARM_ID = "@ID";
    private const string PARM_TIEUDE = "@TIEUDE";
    private const string PARM_NOIDUNG = "@NOIDUNG";
    private const string PARM_NGAYDANG = "@NGAYDANG";
    private const string PARM_NGUOIDANG = "@NGUOIDANG";
    private const string PARM_NGUON = "@NGUON";
    private const string PARM_IMAGE1 = "@IMAGE1";
    private const string PARM_IMAGE2 = "@IMAGE2";
    private const string PARM_IMAGE3 = "@IMAGE3";
    private const string PARM_IMAGE4 = "@IMAGE4";
    private const string PARM_IMAGE5 = "@IMAGE5";

    private News NewsManipulate(SqlDataReader reader)
    {
        News n = new News();
        if (!reader.IsDBNull(0))
            n.Id = reader.GetInt32(0);
        if (!reader.IsDBNull(1))
            n.TieuDe = reader.GetString(1);
        if (!reader.IsDBNull(2))
            n.NoiDung = reader.GetString(2);
        if (!reader.IsDBNull(3))
            n.NgayDang = reader.GetString(3);
        if (!reader.IsDBNull(4))
            n.NguoiDang = reader.GetString(4);
        if (!reader.IsDBNull(5))
            n.Nguon = reader.GetString(5);
        if (!reader.IsDBNull(6))
            n.Image1 = reader.GetString(6);
        if (!reader.IsDBNull(7))
            n.Image2 = reader.GetString(7);
        if (!reader.IsDBNull(8))
            n.Image3 = reader.GetString(8);
        if (!reader.IsDBNull(9))
            n.Image4 = reader.GetString(9);
        if (!reader.IsDBNull(10))
            n.Image5 = reader.GetString(10);
        return n;
    }

    public IList<News> GetAll(string tieuDe)
    {
        SqlParameter[] parms;
        parms = new SqlParameter[] { new SqlParameter(PARM_TIEUDE, SqlDbType.NVarChar) };
        parms[0].Value = "%" + tieuDe + "%";
        IList<News> result = new List<News>();
        using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
            CommandType.Text, SQL_SELECT_NEWS, parms))
        {
            while (reader.Read())
            {
                News n = NewsManipulate(reader);
                result.Add(n);
            }
        }
        return result;
    }

    public IList<News> GetByTitle(string tieuDe)
    {
        SqlParameter[] parms;
        parms = new SqlParameter[] { new SqlParameter(PARM_TIEUDE, SqlDbType.NVarChar) };
        parms[0].Value = "%" + tieuDe + "%";

        IList<News> result = new List<News>();
        using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
            CommandType.Text, SQL_SELECT_NEWS_BYTITLE, parms))
        {
            while (reader.Read())
            {
                News n = NewsManipulate(reader);
                result.Add(n);
            }
        }
        return result;
    }

    public bool Delete(int id)
    {
        SqlParameter[] parms;
        parms = new SqlParameter[] { new SqlParameter(PARM_ID, SqlDbType.Int) };
        parms[0].Value = id;

        try
        {
            int result = 0;
            result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_DELETE_NEWS, parms);
            return (result > 0 ? true : false);
        }
        catch (Exception e)
        {
            throw e;
        }
        return false;
    }

    public bool Update(News n)
    {
        SqlParameter[] parms;
        parms = new SqlParameter[] {
            new SqlParameter(PARM_ID, SqlDbType.Int),
            new SqlParameter(PARM_TIEUDE, SqlDbType.NVarChar),
            new SqlParameter(PARM_NOIDUNG, SqlDbType.NText),
            new SqlParameter(PARM_NGAYDANG, SqlDbType.NChar),
            new SqlParameter(PARM_NGUOIDANG, SqlDbType.NVarChar),
            new SqlParameter(PARM_NGUON, SqlDbType.NChar),
            new SqlParameter(PARM_IMAGE1, SqlDbType.NChar),
            new SqlParameter(PARM_IMAGE2, SqlDbType.NChar),
            new SqlParameter(PARM_IMAGE3, SqlDbType.NChar),
            new SqlParameter(PARM_IMAGE4, SqlDbType.NChar),
            new SqlParameter(PARM_IMAGE5, SqlDbType.NChar)};
        parms[0].Value = n.Id;
        parms[1].Value = n.TieuDe;
        parms[2].Value = n.NoiDung;
        parms[3].Value = n.NgayDang;
        parms[4].Value = n.NguoiDang;
        parms[5].Value = n.Nguon;
        parms[6].Value = n.Image1;
        parms[7].Value = n.Image2;
        parms[8].Value = n.Image3;
        parms[9].Value = n.Image4;
        parms[10].Value = n.Image5;

        try
        {
            int result = 0;
            result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_UPDATE_NEWS, parms);
            return (result > 0 ? true : false);
        }
        catch (Exception e)
        {
            throw e;
        }
        return false;
    }

    public bool UpdateTuyenDung(News n)
    {
        SqlParameter[] parms;
        parms = new SqlParameter[] {
            new SqlParameter(PARM_ID, SqlDbType.Int),
            new SqlParameter(PARM_TIEUDE, SqlDbType.NVarChar),
            new SqlParameter(PARM_NOIDUNG, SqlDbType.NText),
            new SqlParameter(PARM_NGAYDANG, SqlDbType.NChar)};
        parms[0].Value = n.Id;
        parms[1].Value = n.TieuDe;
        parms[2].Value = n.NoiDung;
        parms[3].Value = n.NgayDang;

        try
        {
            int result = 0;
            result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_UPDATE_TUYENDUNG, parms);
            return (result > 0 ? true : false);
        }
        catch (Exception e)
        {
            throw e;
        }
        return false;
    }

    public bool Insert(News n)
    {
        SqlParameter[] parms;
        parms = new SqlParameter[] {
            new SqlParameter(PARM_TIEUDE, SqlDbType.NVarChar),
            new SqlParameter(PARM_NOIDUNG, SqlDbType.NText),
            new SqlParameter(PARM_NGAYDANG, SqlDbType.NChar),
            new SqlParameter(PARM_NGUOIDANG, SqlDbType.NVarChar),
            new SqlParameter(PARM_NGUON, SqlDbType.NChar),
            new SqlParameter(PARM_IMAGE1, SqlDbType.NChar),
            new SqlParameter(PARM_IMAGE2, SqlDbType.NChar),
            new SqlParameter(PARM_IMAGE3, SqlDbType.NChar),
            new SqlParameter(PARM_IMAGE4, SqlDbType.NChar),
            new SqlParameter(PARM_IMAGE5, SqlDbType.NChar)};
        parms[0].Value = n.TieuDe;
        parms[1].Value = n.NoiDung;
        parms[2].Value = n.NgayDang;
        parms[3].Value = n.NguoiDang;
        parms[4].Value = n.Nguon;
        parms[5].Value = n.Image1;
        parms[6].Value = n.Image2;
        parms[7].Value = n.Image3;
        parms[8].Value = n.Image4;
        parms[9].Value = n.Image5;

        try
        {
            int result = 0;
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            result = SQLDBHelper.ExecuteNonQuery(conn,
                CommandType.Text, SQL_INSERT_NEWS, parms);
            if (conn.State == ConnectionState.Open)
                conn.Close();
            return (result > 0 ? true : false);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return false;
    }

    public bool InsertTuyenDung(News n)
    {
        SqlParameter[] parms;
        parms = new SqlParameter[] {
            new SqlParameter(PARM_TIEUDE, SqlDbType.NVarChar),
            new SqlParameter(PARM_NOIDUNG, SqlDbType.NText),
            new SqlParameter(PARM_NGAYDANG, SqlDbType.NChar)};
        parms[0].Value = n.TieuDe;
        parms[1].Value = n.NoiDung;
        parms[2].Value = n.NgayDang;

        try
        {
            int result = 0;
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            result = SQLDBHelper.ExecuteNonQuery(conn,
                CommandType.Text, SQL_INSERT_TUYENDUNG, parms);
            if (conn.State == ConnectionState.Open)
                conn.Close();
            return (result > 0 ? true : false);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return false;
    }

    public News GetNewsByID(int id)
    {
        SqlParameter[] parms;
        parms = new SqlParameter[] { new SqlParameter(PARM_ID, SqlDbType.Int) };
        parms[0].Value = id;

        using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
            CommandType.Text, SQL_SELECT_NEWS_BYID, parms))
        {
            if (reader.Read())
            {
                News n = NewsManipulate(reader);
                return n;
            }
        }
        return null;
    }
}
